package com.citywithincity.db.meta.impl.oracle;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Date;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.citywithincity.db.Db;
import com.citywithincity.db.Record;
import com.citywithincity.db.drivers.oracle.OracleDriver;
import com.citywithincity.db.interfaces.Ar;
import com.citywithincity.db.interfaces.SqlDriver;
import com.citywithincity.db.meta.ColumnStruct;
import com.citywithincity.db.meta.DbStructFactory;
import com.citywithincity.db.meta.KeyType;
import com.citywithincity.db.meta.impl.AbsDbStructFactory;
import com.citywithincity.db.meta.impl.DbStruct;

public class OracleDbStruct extends AbsDbStructFactory implements DbStructFactory {

	private String user;

	public OracleDbStruct(SqlDriver driver, String user) {
		super(driver);
		this.user = user;
	}



	@Override
	protected Map<String, String> getTableCommentMap() {
		List<Record> list = Db.ar(getDb()).select("TABLE_NAME,COMMENTS")
				.get("user_tab_comments");

		Map<String, String> result = new HashMap<String, String>();
		for (Record data : list) {
			result.put(data.getString("TABLE_NAME"),
					data.getString("COMMENTS"));
		}

		return result;
	}

	private Set<String> getSequnces() {
		List<Record> allSequnce = Db.ar(getDb()).select("SEQUENCE_NAME")
				.where("SEQUENCE_OWNER", user.toUpperCase()).get("all_sequences");

		Set<String> sequences = new HashSet<String>();
		for (Record record : allSequnce) {
			sequences.add(record.getString("SEQUENCE_NAME"));
		}
		return sequences;
	}

	private Map<String, String> keyTypes;
	private Map<String, String> triggerMap;
	private Map<String,List<Record>> columnsMap;
	
	private Map<String, String> getTriggers() {
		List<Record> triggers = Db.ar(getDb()).select("all_tables.table_name,trigger_name")
				.join("all_triggers",
						"all_triggers.table_name=all_tables.table_name and all_triggers.trigger_name=CONCAT(all_tables.table_name,'_INCREASE')")
				.where("all_tables.owner", user.toUpperCase()).get("all_tables");
		Map<String, String> triggerMap = new HashMap<String, String>();
		for (Record record : triggers) {
			triggerMap.put(record.getString("TABLE_NAME"), record.getString("TRIGGER_NAME"));
		}
		return triggerMap;
	}
	
	protected Map<String, List<Record>> getColumns(){
		Map<String,List<Record>> columnsMap = this.columnsMap;
		if(columnsMap==null){
			columnsMap = new HashMap<String, List<Record>>(100);
			List<Record> columns = Db.ar(getDb())
					.select("cols.table_name,cols.column_name,cols.DATA_PRECISION,cols.NULLABLE,cols.DATA_DEFAULT,cols.DATA_TYPE,cols.DATA_LENGTH,COMMENTS")
					.join("user_tables", "user_tables.table_name=cols.table_name")
					.join("user_col_comments",
							"cols.COLUMN_NAME=user_col_comments.column_name and cols.TABLE_name=user_col_comments.TABLE_name",
							Ar.LEFT)
					.get("cols");
			
			for (Record record : columns) {
				String table = record.getString("TABLE_NAME");
				List<Record> l = columnsMap.get(table);
				if (l == null) {
					l = new ArrayList<Record>();
					columnsMap.put(table, l);
				}
				l.add(record);
			}
			this.columnsMap = columnsMap;
		}
		return columnsMap;
		
	}

	protected Map<String, String> getKeyTypes(){
		Map<String, String> keyTypes = this.keyTypes;
		if(keyTypes==null){
			List<Record> consts = Db.ar(getDb())
					.select("user_cons_columns.constraint_name,user_cons_columns.table_name, user_cons_columns.column_name,user_constraints.constraint_type")
					.join("user_constraints", "user_cons_columns.constraint_name = user_constraints.constraint_name")
					.where("user_constraints.constraint_type", "P").orWhere("user_constraints.constraint_type", "U")
					.get("user_cons_columns");
			keyTypes = new HashMap<String, String>();
			for (Record record : consts) {
				
				String key = record.getString("CONSTRAINT_TYPE");
				keyTypes.put(new StringBuilder().append(record.getString("TABLE_NAME"))
						.append(record.getString("COLUMN_NAME")).toString(), key);
			}
			this.keyTypes = keyTypes;
		}
		return keyTypes;
	}
	
	
	protected Map<String, String> getIndexTypes(){
		List<Record> indexs = Db.ar(getDb())
				.select("user_ind_columns.table_name,user_ind_columns.column_name,user_indexes.index_type")
				.join("user_indexes", "user_ind_columns.index_name = user_indexes.index_name").get("user_ind_columns");
		
		Map<String, String> indexTypes = new HashMap<String, String>(indexs.size());
		for (Record record : indexs) {
			indexTypes.put(new StringBuilder().append(record.getString("TABLE_NAME"))
					.append(record.getString("COLUMN_NAME")).toString(), "I");
		}
		return indexTypes;
	}

	@Override
	protected List<ColumnStruct> getColumnStructs(String tableName) {
		Map<String, String> keyTypes = getKeyTypes();
		List<Record> list = getColumns().get(tableName.toUpperCase());
		if(list==null){
			throw new RuntimeException("找不到表"+tableName+"的结构");
		}
		List<ColumnStruct> structs = new ArrayList<ColumnStruct>();
		for (Record record : list) {
			structs.add(parse(record, tableName, keyTypes));
		}
		return structs;
	}
	
	

	protected ColumnStruct parse(Record record,String table,Map<String, String> keyTypes){
		ColumnStruct struct = new ColumnStruct(table);
		struct.setName(record.getString("COLUMN_NAME"));
		if(triggerMap == null){
			triggerMap = getTriggers();
		}
		
		struct.setComment(record.getString("COMMENTS"));
		String keyType = keyTypes.get(new StringBuilder().append(table.toUpperCase()).append(struct.getName()).toString());
		if (keyType != null) {
			if (keyType.equals("P")) {
				struct.setKeyType(KeyType.Primary);
				if (triggerMap.containsKey(table.toUpperCase())) {
					struct.setAuto(true);
				}
			} else if (keyType.equals("U")) {
				struct.setKeyType(KeyType.Unique);
			} else if (keyType.equals("I")) {
				struct.setKeyType(KeyType.Index);
			}
		}

		struct.setDefaultValue(record.getString("DATA_DEFAULT"));
		struct.setNullable(record.getString("NULLABLE").equals("Y"));
		struct.setMaxLen(record.getLong("DATA_LENGTH"));
		struct.setClazz(convertType(record.getString("DATA_TYPE"),table));
		struct.setRawType(record.getString("DATA_TYPE"));
		struct.setType(convertTypeToInt(record.getString("DATA_TYPE")));
		return struct;
	}
	
	
	private int convertTypeToInt(String type) {
		if (type.equals("NUMBER")) {
			return Types.NUMERIC;
		}
		if (type.equals("VARCHAR2")) {
			return Types.VARCHAR;
		}
		if (type.equals("NVARCHAR2")) {
			return Types.NVARCHAR;
		}
		if (type.equals("CLOB")) {
			return Types.CLOB;
		}
		if (type.equals("BLOB")) {
			return Types.BLOB;
		}
		if (type.equals("DATE")) {
			return Types.DATE;
		}
		if (type.equals("CHAR")) {
			return Types.CHAR;
		}
		if(type.equals("TIMESTAMP")){
			return Types.TIMESTAMP;
		}
		throw new RuntimeException("不支持的类型" + type);
	}

	private Class<?> convertType(String type, String table) {
		if (type.equals("NUMBER")) {
			return BigDecimal.class;
		}
		if (type.equals("VARCHAR2")) {
			return String.class;
		}
		if (type.equals("NVARCHAR2")) {
			return String.class;
		}
		if (type.equals("CLOB")) {
			return Clob.class;
		}
		if (type.equals("BLOB")) {
			return Blob.class;
		}
		if (type.equals("DATE")) {
			return Date.class;
		}
		if (type.equals("CHAR")) {
			return String.class;
		}
		if(type.equals("TIMESTAMP")){
			return Timestamp.class;
		}
		throw new RuntimeException("不支持的类型" + type + " 在表中:" + table);
	}

	@Override
	public DbStruct getDbStruct() {
		/*
		Map<String, List<ColumnStruct>> columnMap = createColumnMap();
		Map<String, String> commentMap = getTableCommentMap();
		List<TableStruct> list = new ArrayList<TableStruct>();
		for (Entry<String, List<ColumnStruct>> entry : columnMap.entrySet()) {
			String key = entry.getKey();
			String comment = commentMap.get(key);
			TableStruct tableStruct = new TableStruct(entry.getValue(), key, comment);
			list.add(tableStruct);
		}
		
		DbStruct dbStruct = new DbStruct();
		dbStruct.setTables(list);
		dbStruct.setSequences(getSequnces());
		dbStruct.setTriggers(getTriggers());

		return dbStruct;*/
		return null;
	}



	@Override
	protected String getDbType(ColumnStruct struct) {
		switch (struct.getType()) {
		case Types.INTEGER:
			return "NUMBER";
		case Types.SMALLINT:
			return "NUMBER";
		case Types.BIGINT:
			return "NUMBER";
		case Types.VARCHAR:
			return new StringBuilder().append("VARCHAR2(")
					.append(struct.getMaxLen()).append(")").toString();
		case Types.NVARCHAR:
			return new StringBuilder().append("NVARCHAR2(")
					.append(struct.getMaxLen()).append(")").toString();
		case Types.DATE:
			return "date";
		case Types.TIME:
			return "time";
		case Types.BOOLEAN:
			return "NUMBER";
		case Types.TIMESTAMP:
			return "timestamp";
		case Types.CHAR:
			return new StringBuilder().append("CHAR(")
					.append(struct.getMaxLen()).append(")").toString();
		case Types.CLOB:
			if(struct.getMaxLen()==65535){
				return "CLOB";
			}
			if(struct.getMaxLen()==0){
				return "CLOB";
			}
		case Types.BLOB:
			return "BLOB";
		case Types.DOUBLE:
			return "DOUBLE";
		case Types.NUMERIC:
			return "NUMBER";
		default:
			throw new RuntimeException("不支持的类型"+struct.getType());
		}
	}








}
